import copy
import datetime
from time import sleep

import requests
import  login_info.login_info as login
from selenium import webdriver
from selenium.webdriver.support.wait import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from tools.get_sql import getsql
class river_table_info():
    def __init__(self):
        self.emr_user = login.emr_user
        self.emr_password = login.emr_password
        self.get_cookie_url = login.studio_get_cookie_url
        self.send_job_url = login.send_job_url
        self.chk_job_stats_url_head = login.chk_job_stats_url_head
        self.get_job_result_url_head = login.get_result_url
        self.cookies = self.get_cookies()
        # self.dmall_tables = list()
        # self.emr_dict = dict()
        # self.dmall_dict = dict()
        # self.result = list()
        # self.result_d = dict()
        # self.dmall_dept_tables = list()
    def get_cookies_from_txt(self):
        f = open("login_info/cookie.txt", "r")
        str = f.read()
        if(str==''):
            print('cookie.txt中没有内容')
        else:
            print('接到了数据:',str)
        return str
    def put_cookies_in_txt(self,cookie):
        f = open("login_info/cookie.txt", "w")
        cookie = str(cookie)
        f.write(cookie)
    def put_sql_in_txt(self,sql):
        f = open("sql.txt", "w")
        cookie = str(sql)
        f.write(cookie)
    def get_cookies(self):
        cookie = self.get_cookies_from_txt()
        if(cookie ==''):
            return self.get_brower_cookies()
        print(cookie)
        return  cookie
    def get_brower_cookies(self):
        chrome_options = webdriver.ChromeOptions()
        # chrome_options.add_argument('--headless')
        # chrome_options.add_argument('--disable-gpu')
        # chrome_options.add_argument('--no-sandbox')
        # chrome_options.add_argument('--hide-scrollbars')
        browser = webdriver.Chrome(options=chrome_options)



        browser.implicitly_wait(5)
        browser.get(self.get_cookie_url)
        # print(browser.page_source)

        WebDriverWait(browser, 10).until(EC.visibility_of(browser.find_element_by_id('login-by')))
        login_name = browser.find_element_by_id('login-by')
        login_name.send_keys(self.emr_user)
        WebDriverWait(browser, 10).until(EC.visibility_of(browser.find_element_by_id('password')))
        login_psd = browser.find_element_by_id('password')
        login_psd.send_keys(self.emr_password)

        login_btn = browser.find_element_by_id('btn-submit-login')
        login_btn.click()

        simi = browser.find_element_by_id('details-button')
        simi.click()
        go = browser.find_element_by_id('proceed-link')
        go.click()
        sleep(1)
        browser_cookie = browser.get_cookies()
        cookie_name = browser_cookie[0]['name']
        cookie_value = browser_cookie[0]['value']
        head_cookie = f'{cookie_name}={cookie_value}'
        print('get cookies success')
        # browser.close()
        self.put_cookies_in_txt(head_cookie)
        print('put ok')
        print('cookie:',head_cookie)
        return head_cookie
    def step_1(self, sql_cmd):
        # 模拟浏览器，把SQL命令发送给服务器
        # 返回SQL命令的实例ID
        headers_1 = login.heard_sql_id
        if self.cookies != '':
            headers_1['Cookie'] = self.cookies
        #发送的数据
        data = login.data
        if sql_cmd:
            data['actionConf']['sql'] = sql_cmd
        result_1 = requests.post(url=self.send_job_url, headers=headers_1, json=data)
        if result_1.status_code == 200:
            # print(result_1.text)
            result_json = result_1.json()
            status = result_json['retCode']
            if status == 200:
                instanceid = result_json['retData']['instanceId']
            return instanceid
        else:
            raise Exception('登陆错误，错误码为：',result_1.status_code)

    def step_2(self, instanceid):
        # 接收实例ID，查询执行状态
        headers_2 = login.header2
        if self.cookies != '':
            headers_2['Cookie'] = self.cookies

        url_2 = self.chk_job_stats_url_head.format(instanceid)

        # print(url_2)
        result_2 = requests.get(url=url_2, headers=headers_2)
        if result_2.status_code == 200:
            sql_step = result_2.json()
            if(sql_step['retData']['stmtProgresses'][0]['state']=='FINISHED'):
                success_sql = str(getsql(result_2.text))
                f = open("data_log/success_sql.txt", "a+")
                f.write('\n____________________________________________________________\n')
                f.write('user: '+login.emr_user +'\n')
                f.write('operate_time: '+datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')+'\n')
                f.write(success_sql+"\n")
                f.write('\n____________________________________________________________\n')
                f.close()
        else:
            print(result_2.status_code)

        # sql_status = sql_step['retData']['result']
        # print('check sql status success')
        state = sql_step['retData']['stmtProgresses'][0]['state']
        return state

    def step_3(self, instanceid):
        # 接收实例ID，获取执行结果
        headers_3 = {"Accept": "application/json, text/plain, */*","Accept-Encoding": "gzip, deflate",
        "Accept-Language": "zh-CN,zh;q=0.9",
        "Connection": "keep-alive",
        "Cookie": "DataStudio__session=CB408D3980A2EB185F9BED3805F4CD8F",
        "DNT": "1",
        "Host": "10.45.13.122:30104",
        "Referer": "http://10.45.13.122:30104/view/index.html",
        "User-Agent": "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.70 Safari/537.36",
        "X-Requested-With": "XMLHttpRequest"}
        if self.cookies != '':
            headers_3['Cookie'] = self.cookies

        # url_3_head = 'http://10.45.13.122:30104/api/v1/dtalent/ddp/worknode/instance/{}?mode=manual&show=log&workspaceName=ykdsj_zxk_dev'
        url_3 = self.get_job_result_url_head.format(instanceid)
        result_3 = requests.get(url=url_3, headers=headers_3)
        if result_3.status_code == 200:
            result = result_3.text
            print('get sql result success')
            # print(type(result))
            return result_3.json()

        else:
            print(result_3.status_code)

    def run_emr_sql(self,sql):
        print(sql)
        instance = self.step_1(sql)
        status = self.step_2(instance)
        while status != 'FINISHED':
            if status == 'ERROR':
                print('错误的sql是：',sql)
                return
                # f = open("fail_sql.txt", "a+")
                # cookie = str('错误的sql是：',sql,'\n')
                # f.write(cookie)
                # raise Exception('执行sql发生错误')
            sleep(3)
            # print(status)
            status = self.step_2(instance)

        result = self.step_3(instance)
        return result

    def get_rows(self):
        print('输入表名:')
        sql=input('')
        self.table_name = sql
        desc_sql = 'desc '+sql
        result = self.run_emr_sql(desc_sql)
        detail = result['retData']['detail']
        if_success = detail[1:6]
        if(if_success!=' 运行成功'):
            raise Exception('运行失败')
        rows = result['retData']['stmtResults'][0]['sqlResult']['rows']
        return rows
    # def drop_table(self):
    #     print('输入表名:')
    #     sql = input('')
    #     self.table_name = sql
    #     desc_sql = 'drop table ' + sql
    #     print(desc_sql)
    #     result = self.run_emr_sql(desc_sql)
    #     detail = result['retData']['detail']
    #     if_success = detail[1:6]
    #     print(if_success)
    #     if (if_success != ' 运行成功'):
    #         raise Exception('运行失败')
    #     rows = result['retData']['stmtResults'][0]['sqlResult']['rows']
    #     return rows
    def get_columns(self,list_s):
        for x in list_s:
            print('数据类型:', x[1], '--', '列名', x[0], '--', '备注:', x[2])
    def make_sql(self,name_list):

        sql_middle= '\t'+'(select count(1) as total from '+ self.table_name +')'+ ','+'\n'
        for index,col_name in enumerate(name_list):
            col = col_name[0]
            if(index != len(name_list)-1):
                sql_middle += str('\t'+ \
                                  '(select count(1) as {}_num from ' + self.table_name + ' where {} is not NULL)' + 'a' + str(index) + ','+'\n').format(col, col)
            else:
                sql_middle += str('\t'+ \
                                  '(select count(1) as {}_num from ' + self.table_name + ' where {} is not NULL)' + 'a' + str(index)).format(col, col)
        sql = 'select * from \n{}'.format(sql_middle)
        self.put_sql_in_txt(sql)
        return sql

    # def insert_over_wirte(self):
    #     self.std_name = self.table_name[3:]
    #     sql = 'INSERT OVERWRITE table {} \n select '.format(self.std_name,self.table_name)


    def create_new_table(self):
        std_table = 'std'+self.table_name[3:]
        std_table_err = std_table+'_err'
        self.std = std_table
        self.err = std_table_err
        # self.std_table = 'create table  {} like {}'.format(std_table,self.table_name)
        # self.err_table = 'create table  {} like {}'.format(std_table_err,self.table_name)
    def win(self,sql):
        rows_result = self.run_emr_sql(sql)
        detail = rows_result['retData']['detail']
        if_success = detail[1:6]
        if (if_success != ' 运行成功'):
            raise Exception('运行失败')
        # total = rows_result['retData']['stmtResults'][0]['sqlResult']['rows'][0][0]
        result = rows_result['retData']['stmtResults'][0]['sqlResult']['rows'][0]
        for x in result:
            print(x)

        # if(total == '0'):
        #     self.create_new_table()
        #     # print('ininininininininni')
        #     # self.run_emr_sql(self.std_table)
        #     # self.run_emr_sql(self.err_table)
        # else:
        #     print("暂时无服务")
        #     self.create_new_table()
        #     # self.run_emr_sql(self.std_table)
        #     # self.run_emr_sql(self.err_table)
        #     result_dict = rows_result['retData']['stmtResults'][0]['sqlResult']['rows'][0]
        #     full_dict = copy.deepcopy(result_dict)
        #     small_dict = copy.deepcopy(result_dict)
        #     small_dict = get_small_dict(small_dict,total)
        #     full_dict = get_all_dict(full_dict,total)
        #     self.small = small_dict
        #     self.full = full_dict
        #     self.as_select(rows)

    # def as_select(self,rows):
    #     # sql = "INSERT OVERWRITE table {} \n \t select * FROM (".format(self.std)
    #     sql = "INSERT OVERWRITE table {} \n \t select ".format(self.std)
    #     sql_middle='\n\t\t\tselect'
    #     for index,col_name in enumerate(rows):
    #         col = col_name['col_name']
    #         if(index != len(rows)-1):
    #             sql +=str(col+',')
    #         else:
    #             sql += str(col+' ')
    #     sql += '\n\t\tfrom ( '
    #     list_1 = list(trim_num_full(del_total(self.full)).keys())
    #     list_2 = list(trim_num_small(self.small).keys())
    #     print('123')
    #     for index,key in enumerate(list_1):
    #         sql_middle += '\n\t\t\t\t' + key + ','
    #
    #     for index, key in enumerate(list_2):
    #         if (index != len(list_2) - 1):
    #             sql_middle += '\n\t\t\t\t' + 'CASE WHEN ' +key+ ' is null then ' + '\'待填写\' ' + 'else '+ key +' end as ' +key +','
    #         if (index == len(list_2) - 1):
    #             sql_middle += '\n\t\t\t\t' + 'CASE WHEN ' +key+ ' is null then ' + '\'待填写\' ' + 'else '+ key +' end as ' +key+' from '+self.table_name+'\n'
    #
    #     print(sql_middle)
    #     sql_end = ')t'
    #     end = sql + sql_middle + sql_end
    #     print(end)


if __name__ == '__main__':
    a = river_table_info()
    # a.drop_table()
    rows = a.get_rows()
    a.get_columns(rows)
    # # # #查询出total的个数
    sql = a.make_sql(rows)
    a.win(sql)

